﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace UFIDA.U8.YYNBJY.DBHelp
{
    /// <summary>
    /// OA数据库
    /// </summary>
    public class DBHelpDAL
    {
        public static string ConnectionString = "";// ConfigurationManager.ConnectionStrings["ConnectionOA"].ToString();

        #region CURD
        public static SqlConnection iConnection(string connectionString, string safeSql)
        {
            SqlConnection connection = null;
            try
            {
                if (connection == null)
                {
                    connection = new SqlConnection(connectionString);

                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Broken)
                {
                    connection.Close();
                    connection.Open();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据连接失败！" + safeSql, "信息提示");
                WriteTxt(ex.ToString());
            }
            return connection;
        }
        public static SqlConnection iConnection(string connectionString, string safeSql, int s)
        {
            SqlConnection connection = null;
            try
            {
                if (connection == null)
                {
                    connection = new SqlConnection(connectionString);

                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Broken)
                {
                    connection.Close();
                    connection.Open();
                }
            }
            catch (Exception ex)
            {
                if (s == 1)
                {
                    MessageBox.Show("数据连接失败！" + safeSql, "信息提示");
                }
                WriteTxt(ex.ToString());
            }
            return connection;
        }
        public static SqlConnection iConnection(string connectionString)
        {
            SqlConnection connection = null;
            try
            {
                if (connection == null)
                {
                    connection = new SqlConnection(connectionString);
                    //connection.ConnectionTimeout = 300;
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Broken)
                {
                    connection.Close();
                    connection.Open();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据连接失败！", "信息提示");
                WriteTxt(ex.ToString());
            }
            return connection;
        }

        //查询 DataTable
        public static DataTable GetDataTable(string safeSql)//, SqlParameter[] paras, CommandType type = CommandType.Text)
        {
            try
            {
                //DBHelp.DBHelpDAL.WriteTxt3("getdatatable接收到的数据库链接方式:" + ConnectionString);
                SqlConnection Connection = iConnection(ConnectionString, safeSql);
                DataTable dt = new DataTable();
                SqlDataAdapter sda = new SqlDataAdapter(safeSql, ConnectionString);
                sda.Fill(dt);
                Connection.Close();
                return dt;
            }
            catch (Exception ex)
            {
                WriteTxt(safeSql + "\n" + ex.ToString());
                return null;
            }
        }
        public static DataTable GetDataTable2(string safeSql, string ConnectionString)//, SqlParameter[] paras, CommandType type = CommandType.Text)
        {
            try
            {
                SqlConnection Connection = iConnection(ConnectionString, safeSql);
                DataTable dt = new DataTable();
                SqlDataAdapter sda = new SqlDataAdapter(safeSql, ConnectionString);
                sda.Fill(dt);
                Connection.Close();
                return dt;
            }
            catch (Exception ex)
            {
                WriteTxt(safeSql + "\n" + ex.ToString());
                return null;
            }
        }
        public static DataTable GetDataTable2(string safeSql, string ConnectionString, int s)//, SqlParameter[] paras, CommandType type = CommandType.Text)
        {
            try
            {
                SqlConnection Connection = iConnection(ConnectionString, safeSql, s);
                DataTable dt = new DataTable();
                SqlDataAdapter sda = new SqlDataAdapter(safeSql, ConnectionString);
                sda.Fill(dt);
                Connection.Close();
                return dt;
            }
            catch (Exception ex)
            {
                WriteTxt(safeSql + "\n" + ex.ToString());
                return null;
            }
        }
        //查询 DataSet
        public static DataTable GetDataSet(string safeSql)
        {
            try
            {
                SqlConnection Connection = iConnection(ConnectionString, safeSql);
                DataSet dt = new DataSet();
                SqlCommand cmd = new SqlCommand(safeSql, Connection);
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                sda.Fill(dt);
                Connection.Close();
                return dt.Tables[0];
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据处理失败！", "信息提示");
                WriteTxt(safeSql + "\n" + ex.ToString());
                //Connection.Close();
                return null;
            }
        }

        public static int ExecuteCommand(string ConnectionString, params SqlParameter[] values)
        {
            try
            {
                SqlConnection Connection = iConnection(ConnectionString);
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = Connection;
                cmd.CommandText = "Pro_UpdateBooksCatagory";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(values);
                int result = cmd.ExecuteNonQuery();
                Connection.Close();
                return result;
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据处理失败！", "信息提示");
                WriteTxt(ex.ToString());
                //Connection.Close();
                return 0;
            }
        }

        //执行一条数据
        public static int ExecuteSqlTran(string safeSql)
        {
            try
            {
                SqlConnection Connection = iConnection(ConnectionString, safeSql);
                SqlCommand cmd = new SqlCommand(safeSql, Connection);
                int result = cmd.ExecuteNonQuery();
                Connection.Close();
                return result;
            }
            catch (Exception ex)
            {
                //MessageBox.Show("数据处理失败！", "信息提示");
                WriteTxt(safeSql + "\n" + ex.ToString());
                //Connection.Close();
                return 0;
            }

        }
        public static int ExecuteSqlTran2(string safeSql, string ConnectionString)
        {
            try
            {
                //WriteTxt("状态修改1");
                SqlConnection Connection = iConnection(ConnectionString, safeSql);
                SqlCommand cmd = new SqlCommand(safeSql, Connection);
                int result = cmd.ExecuteNonQuery();
                Connection.Close();
                return result;
            }
            catch (Exception ex)
            {
                //WriteTxt("状态修改2");
                //MessageBox.Show("数据处理失败！", "信息提示");
                WriteTxt(safeSql + "\n" + ex.ToString());
                //Connection.Close();
                return 0;
            }

        }

        //执行多条数据
        public static int ExecuteSqlTranS(List<string> sqllist)
        {
            try
            {
                SqlConnection Connection = iConnection(ConnectionString);
                SqlCommand cmd = new SqlCommand();
                SqlTransaction mytran = Connection.BeginTransaction();

                //绑定数据库连接和事务对象
                cmd.Connection = Connection;
                cmd.Transaction = mytran;
                try
                {
                    foreach (string sql in sqllist)
                    {
                        WriteTxt(cmd.CommandText + "\n" + sql);
                        cmd.CommandText = sql;
                        //MessageBox.Show(sql, "信息提示");
                        cmd.ExecuteNonQuery();
                    }
                    mytran.Commit();
                    Connection.Close();
                    return 1;
                }
                catch (Exception ex)
                {
                    //MessageBox.Show(ex.ToString(), "信息提示");
                    WriteTxt(cmd.CommandText + "\n" + ex.ToString());
                    mytran.Rollback();
                    Connection.Close();
                    return 0;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据处理失败！", "信息提示");
                WriteTxt(ex.ToString());
                //Connection.Close();
                return 0;
            }
        }

        #endregion

        #region 报错日志
        public static void WriteTxt(string error)
        {
            DateTime date = new DateTime();

            date = DateTime.Now;

            string filename = "error" + date.Year.ToString() + date.Month.ToString() + date.Day.ToString() + ".txt";
            string path = Application.StartupPath + "\\log\\" + filename;
            if (!File.Exists(path))
            {
                using (StreamWriter sw = File.CreateText(path))
                {
                    sw.WriteLine("/******************************************************************/");
                    sw.WriteLine("/* " + date.ToString());
                    sw.WriteLine(error);
                    sw.WriteLine("/******************************************************************/");
                }
            }
            else
            {
                StreamReader sr = File.OpenText(path);
                string text = sr.ReadToEnd();//这个是把文件从头读到结尾
                sr.Close();

                File.Delete(path);
                using (StreamWriter sw = new StreamWriter(path))
                {
                    sw.WriteLine(text);
                    sw.WriteLine("/******************************************************************/");
                    sw.WriteLine("/* " + date.ToString());
                    sw.WriteLine(error);
                    sw.WriteLine("/******************************************************************/");
                }
            }
        }
        public static void WriteTxt2(string error, string name)
        {
            DateTime date = new DateTime();

            date = DateTime.Now;

            string filename = name + date.Year.ToString() + date.Month.ToString() + date.Day.ToString() + ".txt";
            string path = Application.StartupPath + "\\log\\" + filename;
            if (!File.Exists(path))
            {
                using (StreamWriter sw = File.CreateText(path))
                {
                    sw.WriteLine("/******************************************************************/");
                    sw.WriteLine("/* " + date.ToString());
                    sw.WriteLine(error);
                    sw.WriteLine("/******************************************************************/");
                }
            }
            else
            {
                StreamReader sr = File.OpenText(path);
                string text = sr.ReadToEnd();//这个是把文件从头读到结尾
                sr.Close();

                File.Delete(path);
                using (StreamWriter sw = new StreamWriter(path))
                {
                    sw.WriteLine(text);
                    sw.WriteLine("/******************************************************************/");
                    sw.WriteLine("/* " + date.ToString());
                    sw.WriteLine(error);
                    sw.WriteLine("/******************************************************************/");
                }
            }
        }

        public static void WriteTxt3(string error)
        {
            DateTime date = new DateTime();

            date = DateTime.Now;

            string filename = "error" + date.Year.ToString() + date.Month.ToString() + date.Day.ToString() + ".txt";
            string path = System.Windows.Forms.Application.StartupPath + "\\logpz\\" + filename;
            if (!File.Exists(path))
            {
                using (StreamWriter sw = File.CreateText(path))
                {
                    sw.WriteLine("/******************************************************************/");
                    sw.WriteLine("/* " + date.ToString());
                    sw.WriteLine(error);
                    sw.WriteLine("/******************************************************************/");
                }
            }
            else
            {
                StreamReader sr = File.OpenText(path);
                string text = sr.ReadToEnd();//这个是把文件从头读到结尾
                sr.Close();

                File.Delete(path);
                using (StreamWriter sw = new StreamWriter(path))
                {
                    sw.WriteLine(text);
                    sw.WriteLine("/******************************************************************/");
                    sw.WriteLine("/* " + date.ToString());
                    sw.WriteLine(error);
                    sw.WriteLine("/******************************************************************/");
                }
            }
        }

        #endregion

        #region 时间戳,C#格式时间互相转换
        // 时间戳转为C#格式时间
        public static DateTime StampToDateTime(string time)
        {

            time = time.Substring(0, 10);
            double timestamp = Convert.ToInt64(time);
            System.DateTime dateTime = new System.DateTime(1970, 1, 1, 0, 0, 0, 0);
            dateTime = dateTime.AddSeconds(timestamp).ToLocalTime();
            return dateTime;

        }
        // C#格式时间转为时间戳
        public static long DateTimeToStamp(DateTime time)
        {

            DateTime DateStart = new DateTime(1970, 1, 1, 8, 0, 0);
            //int q= Convert.ToInt32((time - DateStart).TotalSeconds);//秒
            long q2 = (long)(time - DateStart).TotalMilliseconds;//毫秒
            return q2;

        }

        #endregion

        public static String TimeToHex(DateTime time)
        {
            StringBuilder sb = new StringBuilder();
            DateTime timeStar = TimeZone.CurrentTimeZone.ToLocalTime(new DateTime(1970, 01, 01));
            TimeSpan timeSpan = time - timeStar;
            uint unitSpan = Convert.ToUInt32(timeSpan.TotalSeconds);
            if (unitSpan < 0)
            {
                return sb.ToString();
            }
            byte[] bytes = System.BitConverter.GetBytes(unitSpan);
            for (int i = bytes.Length - 1; i >= 0; i--)
            {
                sb.Append(bytes[i].ToString("X2"));
            }
            return sb.ToString();
        }
        public static DateTime HexToTime(string HexStr)
        {
            HexStr = HexStr.Replace(" ", "");
            DateTime time = TimeZone.CurrentTimeZone.ToLocalTime(new DateTime(1970, 01, 01));
            if (HexStr.StartsWith("oX") || HexStr.StartsWith("OX"))
            {
                HexStr = HexStr.Substring(2, HexStr.Length - 2);
            }

            if (HexStr.Length % 2 != 0)
            {
                HexStr += " ";
            }
            byte[] bytes = new byte[HexStr.Length / 2];
            for (int i = 0; i < bytes.Length; i++)
            {
                bytes[bytes.Length - 1 - i] = Convert.ToByte(HexStr.Substring(i * 2, 2), 16);
            }
            uint unitSpan = System.BitConverter.ToUInt32(bytes, 0);
            return time.AddSeconds(unitSpan);
        }



    }
}
